package ar.com.android.restoapp.dbHelpers;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLFoodOrderHelper extends SQLiteOpenHelper {
	private static final String DATABASE_NAME = "restoapp.db";
	private static final int DB_VERSION_NUMBER = 2;
	
	public static final String DB_TYPEFOOD_TABLE_NAME = "typefood";
	public static final String DB_FOOD_TABLE_NAME = "food";
	public static final String DB_TABLES_TABLE_NAME = "tables";
	public static final String DB_ORDER_TABLE_NAME = "orderfood";
	public static final String DB_ORDERCHILD_TABLE_NAME = "orderfooditem";
	
	private static final String DB_TYPEFOOD_CREATE_SCRIPT = "CREATE TABLE " + 
													DB_TYPEFOOD_TABLE_NAME +                            
													" (idTypeFood INTEGER PRIMARY KEY, " + 
													"  typeDescription VARCHAR(45),  " +
													"  idMenuFood INTEGER " +
													")";
	
	private static final String DB_TABLES_CREATE_SCRIPT = "CREATE TABLE " + 
														DB_TABLES_TABLE_NAME +                            
														" (idTables INTEGER PRIMARY KEY, " + 
														"  description VARCHAR(45), " + 
														"  state VARCHAR(1), " +
														"  quantityGuest INTEGER, " +
														"  comments TEXT " +
														")";
	
	private static final String DB_FOOD_TABLE_CREATE_SCRIPT = "CREATE TABLE " + 
													DB_FOOD_TABLE_NAME +                            
													" (idFood INTEGER PRIMARY KEY, " + 
													"  description VARCHAR(45), " + 
													"  idTypeFood INTEGER, " +
													"  price DOUBLE " +
													")";
	
	private static final String DB_ORDER_CREATE_SCRIPT = "CREATE TABLE " + 
													DB_ORDER_TABLE_NAME +                            
													" (idOrderFood INTEGER PRIMARY KEY AUTOINCREMENT, " + 
													"  idTable INTEGER, " +
													"  numGuests INTEGER, " +
													"  state VARCHAR(1), " +
													"  dateFrom DATETIME, " +
													"  dateTo DATETIME " +
													")";

	private static final String DB_ORDERCHILD_CREATE_SCRIPT = "CREATE TABLE " + 
														DB_ORDERCHILD_TABLE_NAME +                            
														" (idOrderFoodItem INTEGER PRIMARY KEY AUTOINCREMENT, " + 
														"  idTable INTEGER, " + 
														"  idFood INTEGER, " +
														"  description VARCHAR(45), " + 
														"  quantity DOUBLE, " +
														"  price DOUBLE, " +
														"  state VARCHAR(1) " +
														")";
	
	
	
	private static SQLFoodOrderHelper instance;
	
    public static synchronized SQLFoodOrderHelper getHelper(Context context)
    {
        if (instance == null)
            instance = new SQLFoodOrderHelper(context);

        return instance;
    }
	
	private SQLFoodOrderHelper(Context context) {
		super(context, DATABASE_NAME, null, DB_VERSION_NUMBER);
	}
	
	public void dbClose() {
		this.close();
	}
	public void dbOpen() throws SQLException {
		this.getWritableDatabase();
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			Log.i("onCreate", "Creating the database...");
			db.execSQL(DB_TYPEFOOD_CREATE_SCRIPT);
			db.execSQL(DB_TABLES_CREATE_SCRIPT);
			db.execSQL(DB_FOOD_TABLE_CREATE_SCRIPT);
			db.execSQL(DB_ORDER_CREATE_SCRIPT);
			db.execSQL(DB_ORDERCHILD_CREATE_SCRIPT);	
		} catch (Exception e) {
			Log.e("onCreate", "Error with creating Database Tables: " + e);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.i("onUpgrade", "Update the database...");
		db.execSQL("ALTER TABLE " + DB_ORDER_TABLE_NAME + " ADD COLUMN dateFrom DATETIME");
		db.execSQL("ALTER TABLE " + DB_ORDER_TABLE_NAME + " ADD COLUMN dateTo DATETIME");
		db.execSQL("ALTER TABLE " + DB_TABLES_TABLE_NAME + " ADD COLUMN comments TEXT");
	}
	
	public void beginTransaction()
	{
		try {
			this.getWritableDatabase().beginTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in Begintransaction Method: " + e);
		}		
	}
	
	public void commitTransaction()
	{
		try {
			this.getWritableDatabase().setTransactionSuccessful();
			this.getWritableDatabase().endTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in CommitTransaction Method: " + e);
		}		
	}
	
	public void rollbackTransaction()
	{
		try {
			this.getWritableDatabase().endTransaction();
		} catch (Exception e) {
			Log.e("onCreate", "Error in RollbackTransaction Method: " + e);
		}		
	}
}
